package org.standard.proposal.action;


import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.lang.StringUtils;
import org.standard.base.model.BaseDept;
import org.standard.base.model.BaseRole;
import org.standard.base.model.BaseUser;
import org.standard.commons.struts2.BaseAction;
import org.standard.commons.utils.Constants;
import org.standard.commons.utils.FlipPageInfo;
import org.standard.commons.utils.Utils;
import org.standard.proposal.model.Proposal;
import org.standard.proposal.model.ProposalAgreeUser;
import org.standard.proposal.model.ProposalAgreeUserId;
import org.standard.proposal.model.ProposalDept;
import org.standard.tianjieci.model.Tianjieci;

import com.googlecode.jsonplugin.annotations.JSON;
import com.opensymphony.xwork2.Preparable;


public class ProposalAction extends BaseAction implements Preparable {

	private static final long serialVersionUID = -3681769723873503937L;

	private Proposal proposal;
	private BaseUser user;
	private BaseRole role;
	private FlipPageInfo flipPageInfo = new FlipPageInfo();
	private File[] annex; // 中文
	private String[] annexContentType;
	private String[] annexFileName;

	private Date startDate;
	private Date endDate;
	private String s_proposalNo;
	private String s_proposalClass;
	private String s_proposalSession;
	private String s_proposalCount;
	private String s_proposalBrief;
	private String s_proposalUserNameFirst;
	private String s_proposalPrincipal;
	private String s_workUnit;
	private String s_jieBie;
	private String s_proposalAduitSuggestSelect;
	private String s_proposalRegisterTypeSelect;
	private String s_proposalClassify;
	private String proposalTaketoDeptName;
	private String s_proposalMethod;
	private String s_proposalSatisfy;
	private String s_my;
	private String s_proposalSponsor;
	private String s_proposalNeeddo;
	private String s_proposalSponsorId;
	private String s_proposalNeeddoId;
	private String s_proposalDept;
	private String s_proposalDealStatus;
	private String s_proposalStaffStatus;
	private String s_proposalUndertakeDeptSuggest;
	private String s_proposalTaketoDept;

	private List annexList;
	private BaseDept dept;
	private List list;
	private String type;
	
	private Tianjieci tianjieci;
	
	List<ProposalDept> proposalDepts;
	

	

	public String index() {
		
		if (role.getRoleId().equals(Constants.ROLE_COMMITTEE)) {
			List list = service.findPOJOList("from Tianjieci");
			if (list != null && !list.isEmpty()) {
				tianjieci = (Tianjieci) list.get(0);
				proposal = new Proposal();
				proposal.setProposalSession(tianjieci.getProposalSession());
				proposal.setProposalNo(tianjieci.getProposalNo());
				proposal.setProposalClass(tianjieci.getProposalClass());
				proposal.setProposalCount(tianjieci.getProposalCount());
				proposal.setProposalSubmitDate(new Date());
			}
			return CREATE;
		} else {
			return list();
		}
	}
	
	public String create() {
		
		if (user != null) {
			proposal.setProposalUserIdFirst(user.getUserId());
		}
		proposal.setProposalAduitSuggestSelect("0");
		proposal.setProposalRegisterTypeSelect("0");
		proposal.setProposalClassify("0");
		proposal.setProposalSatisfy("0");
		proposal.setProposalStatus(Constants.PROPOSAL_NONE);
		proposal.setProposalDate(new Date());
		proposal.setProposalTaketoDeptSuggest(Constants.NOT_DONE);
		proposal.setProposalUndertakeDeptSuggest(Constants.NOT_DONE);
		proposal.setProposalGovernmentSuggest(Constants.NOT_DONE);
		proposal.setProposalMethod(Constants.NOT_DONE);
		try {
			service.createPOJOAndAnnex(proposal, request, annex, annexFileName, annexContentType);
		} catch (FileUploadException e) {
			setERROR(e.getMessage());
			e.printStackTrace();
			return CREATE;
		}
		return list();
	}
	public String list() {
//		flipPageInfo = service.findPOJOList(Proposal.class, flipPageInfo);
//		StringBuffer sql = new StringBuffer("select agreeUser.proposal_Agree_User_Id, " +
//				"proposal.proposal_Id, proposal.proposal_No, proposal.proposal_Userid_First " +
//				"from Proposal as proposal " +
//				"left join Proposal_Agree_User as agreeUser " +
//				"on proposal.proposal_Id = agreeUser.proposal_Id " +
//				"and agreeUser.proposal_Agree_User_Id = '"+user.getUserId()+"' ");
//		sql.append(" order by proposal.proposal_Date desc");
		search();
		return LIST;
	}
	public String search() {
		
		user = (BaseUser) session.get(Constants.SESSION_USER);
		StringBuffer sql = new StringBuffer("select proposal, user")
			.append(" from Proposal proposal, BaseUser user")
			.append(" where proposal.proposalUserIdFirst = user.userId");
		if (!Utils.isStringNull(s_proposalClass)) {
			sql.append(" and (proposal.proposalClass like '%" + s_proposalClass+ "%')");
		}
		if (!Utils.isStringNull(s_proposalNo)) {
			sql.append(" and (proposal.proposalNo like '%" + s_proposalNo+ "%')");
		}
		if (!Utils.isStringNull(s_proposalSession)) {
			sql.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
		}
		if (!Utils.isStringNull(s_proposalCount)) {
			sql.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
		}
		if (!Utils.isStringNull(s_proposalBrief)) {
			sql.append(" and proposal.proposalBrief like '%" + s_proposalBrief + "%'");
		}
		if (!Utils.isStringNull(s_proposalUserNameFirst)) {
			sql.append(" and user.userName like '%" + s_proposalUserNameFirst + "%'");
		}
		if (!Utils.isStringNull(s_proposalPrincipal)) {
			sql.append(" and proposal.proposalPrincipal like '%" + s_proposalPrincipal + "%'");
		}
		if (!Utils.isStringNull(s_jieBie)) {
			sql.append(" and user.jieBie like '%" + s_jieBie + "%'");
		}
		if (!Utils.isStringNull(s_workUnit)) {
			sql.append(" and user.workUnit like '%" + s_workUnit + "%'");
		}
		if (!Utils.isStringNull(s_proposalAduitSuggestSelect)) {
			sql.append(" and (proposal.proposalAduitSuggestSelect = '" + s_proposalAduitSuggestSelect+ "')");
		}
		if (!Utils.isStringNull(s_proposalRegisterTypeSelect)) {
			sql.append(" and (proposal.proposalRegisterTypeSelect = '" + s_proposalRegisterTypeSelect+ "')");
		}
		if (!Utils.isStringNull(s_proposalClassify)) {
			sql.append(" and (proposal.proposalClassify = '" + s_proposalClassify+ "')");
		}
		if (!Utils.isStringNull(s_proposalSponsorId)) {
			sql.append(" and (proposal.proposalSponsorId like '%" + s_proposalSponsorId+ "%')");
		}
		if (!Utils.isStringNull(s_proposalNeeddoId)) {
			sql.append(" and (proposal.proposalNeeddoId like '%" + s_proposalNeeddoId+ "%')");
		}
		if (null != startDate) {
			sql.append(" and DATE_FORMAT(proposal.proposalSubmitDate, '%Y-%m-%d') >= '"+Utils.formatDateString(startDate)+"'");
		}
		if (null != endDate) {
			sql.append(" and DATE_FORMAT(proposal.proposalSubmitDate, '%Y-%m-%d') <= '"+Utils.formatDateString(endDate)+"'");
		}
		if (Constants.ROLE_TAKETO_DEPT.equals(user.getRoleId())) {
			sql.append(" and proposal.proposalTaketoDept = '" + user.getProposalTaketoDept() + "'");
		}
		if (!Utils.isStringNull(proposalTaketoDeptName)) {
			sql.append(" and (proposal.proposalTaketoDeptName like '%" + proposalTaketoDeptName+ "%')");
		}
		if (!Utils.isStringNull(s_proposalUndertakeDeptSuggest)) {
			sql.append(" and (proposal.proposalUndertakeDeptSuggest = '" + s_proposalUndertakeDeptSuggest+ "')");
		}
		if (!Utils.isStringNull(s_proposalSatisfy)) {
			sql.append(" and (proposal.proposalSatisfy like '%" + s_proposalSatisfy+ "%')");
		}
		if (!Utils.isStringNull(s_proposalDealStatus)) {
			sql.append(" and (proposal.proposalDealStatus like '%" + s_proposalDealStatus+ "%')");
		}
		if (!Utils.isStringNull(s_proposalStaffStatus)) {
			sql.append(" and (proposal.proposalStaffStatus like '%" + s_proposalStaffStatus+ "%')");
		}
		if (!Utils.isStringNull(s_proposalTaketoDept)) {
			sql.append(" and (proposal.proposalUndertakeDept = '" + s_proposalTaketoDept+ "')");
		}
		//委员
		if (!Utils.isStringNull(s_my) || Constants.ROLE_COMMITTEE.equals(role.getRoleId())) {
			sql.append(" and (proposal.proposalUserIdFirst = '" + user.getUserId()+ "')");
		}
		//工作人员
		if (Constants.ROLE_STAFF.equals(role.getRoleId())) {
//			sql.append(" and proposal.proposalStatus <= '"+Constants.PROPOSAL_STAFF_SUBMIT+"'");
		}
		//承办口
		if (Constants.ROLE_UNDERTAKE_DEPT.equals(role.getRoleId())) {
//			sql.append(" and (proposal.proposalStatus = '"+Constants.PROPOSAL_STAFF_SUBMIT+"'" +
//					" or proposal.proposalStatus = '"+Constants.PROPOSAL_UNDERTAKE_DEPT_SUBMIT+"')");
			sql.append(" and proposal.proposalUndertakeDept = '" + user.getProposalUndertakeDept() + "'");
		}
		//主办
		if (Constants.ROLE_ZHUBAN.equals(role.getRoleId())) {
//			sql.append(" and (proposal.proposalStatus >= '"+Constants.PROPOSAL_UNDERTAKE_DEPT_SUBMIT+"'" +
//							" or proposal.proposalStatus <= '"+Constants.PROPOSAL_REFUSE+"')");
			sql.append(" and proposal.proposalSponsorId = '" + user.getProposalSponsorId() + "'");
		}
		//会办
		if (Constants.ROLE_HUIBAN.equals(role.getRoleId())) {
//			sql.append(" and (proposal.proposalStatus >= '"+Constants.PROPOSAL_UNDERTAKE_DEPT_SUBMIT+"'" +
//					" or proposal.proposalStatus <= '"+Constants.PROPOSAL_REFUSE+"')");
			sql.append(" and proposal.proposalNeeddoId = '" + user.getProposalNeeddoId() + "'");
		}
		sql.append(" order by proposal.proposalSubmitDate desc");
		flipPageInfo = service.findPOJOList(sql.toString(), flipPageInfo);
		
		List list = service.findPOJOList("from Tianjieci");
		if (list != null && !list.isEmpty()) {
			tianjieci = (Tianjieci) list.get(0);
		}
		return LIST;
	}
	public String delete() {
		
		try {
			service.deletePOJOCascadeAnnex(proposal, proposal.getProposalId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return search();
	}
	public String agree() {
		
		ProposalAgreeUser agreeUser = new ProposalAgreeUser();
		ProposalAgreeUserId agreeUserId = new ProposalAgreeUserId();
		agreeUserId.setProposalId(proposal.getProposalId());
		agreeUserId.setProposalAgreeUserId(user.getUserId());
		agreeUser.setId(agreeUserId);
		service.createPOJO(agreeUser);
		return list();
	}
	public String modifyPre(){
		
		if (proposal != null) {
			proposal = (Proposal) service.findPOJOById(Proposal.class, proposal.getProposalId());
			annexList = service.findPOJOList("from Annex where tableName = '"+Proposal.class.getSimpleName()+"' and tableId = '"+proposal.getProposalId()+"'");
			user = (BaseUser) service.findPOJOById(BaseUser.class, proposal.getProposalUserIdFirst());
			
		}
		BaseUser userSession = (BaseUser) session.get(Constants.SESSION_USER);
		//工作人员
		if (role.getRoleId().equals(Constants.ROLE_STAFF)) {
			if (Utils.isStringNull(proposal.getProposalClassifyPerson())) {
				proposal.setProposalClassifyPerson(userSession.getUserName());
			}
		}
		//交办单位
		if (role.getRoleId().equals(Constants.ROLE_TAKETO_DEPT)) {
			if (Utils.isStringNull(proposal.getProposalTaketoDeptName())) {
				proposal.setProposalTaketoDeptName(userSession.getUserName());
			}
		}
		//承办单位
		if (role.getRoleId().equals(Constants.ROLE_UNDERTAKE_DEPT)) {
			if (Utils.isStringNull(proposal.getProposalUndertakeDeptName())) {
				proposal.setProposalUndertakeDeptName(userSession.getUserName());
			}
		}
		//市政府
		if (role.getRoleId().equals(Constants.ROLE_GOVERNMENT)) {
			if (Utils.isStringNull(proposal.getProposalGovernmentSuggestName())) {
				proposal.setProposalGovernmentSuggestName(userSession.getUserName());
			}
		}
		
		//设置案件状态
		if (Constants.NOT_DONE.equals(proposal.getProposalUndertakeDeptSuggest())) {
			proposal.setProposalMethod(Constants.NOT_DONE);
		} else if (Constants.DONE.equals(proposal.getProposalUndertakeDeptSuggest())) {
			proposal.setProposalMethod(Constants.DONE);
		} else {
			proposal.setProposalMethod(Constants.DOING);
		}
		try {
			if (!Utils.isStringNull(proposal.getProposalContent())) {
				proposal.setProposalContentBase64(URLEncoder.encode(proposal.getProposalContent(), "UTF-8"));
			}
			if (!Utils.isStringNull(proposal.getProposalAnswer())) {
				proposal.setProposalAnswerBase64(URLEncoder.encode(proposal.getProposalAnswer(), "UTF-8"));
			}
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
			
		return MODIFY;
	}
	public String modify() {
		
		//委员
		if (role.getRoleId().equals(Constants.ROLE_COMMITTEE)) {
			if (!proposal.getProposalStatus().equals(Constants.PROPOSAL_NONE)) {
				proposal.setProposalStatus(Constants.PROPOSAL_COMMITTEE_SATISFY);
			}
		}
		//工作人员
		if (role.getRoleId().equals(Constants.ROLE_STAFF)) {
//			if (proposal.getProposalStatus().equals(Constants.PROPOSAL_NONE)) {
				proposal.setProposalStatus(Constants.PROPOSAL_STAFF_SUBMIT);
				proposal.setProposalAduitSuggestDate(new Date());
//			}
		}
		//交办单位
		if (role.getRoleId().equals(Constants.ROLE_TAKETO_DEPT)) {
//			if (proposal.getProposalStatus().equals(Constants.PROPOSAL_STAFF_SUBMIT)) {
				proposal.setProposalStatus(Constants.PROPOSAL_TAKETO_DEPT_SUBMIT);
				proposal.setProposalTaketoDeptDate(new Date());
//			}
		}
		//承办单位--修改为工作人员直接提交过来的
		if (role.getRoleId().equals(Constants.ROLE_UNDERTAKE_DEPT)) {
//			if (proposal.getProposalStatus().equals(Constants.PROPOSAL_STAFF_SUBMIT)) {
				proposal.setProposalStatus(Constants.PROPOSAL_UNDERTAKE_DEPT_SUBMIT);
				proposal.setProposalUndertakeDeptName(user.getUserName());
				proposal.setProposalUndertakeDeptDate(new Date());
				if (!Utils.isStringNull(proposal.getProposalRefuseChengban())) {
					proposal.setProposalStatus(Constants.PROPOSAL_REFUSE_CHENGBANK);
				}
//			}
		}
		//市政府
		if (role.getRoleId().equals(Constants.ROLE_GOVERNMENT)) {
			if (proposal.getProposalStatus().equals(Constants.PROPOSAL_UNDERTAKE_DEPT_SUBMIT)) {
				proposal.setProposalStatus(Constants.PROPOSAL_GOVERNMENT_SUBMIT);
				proposal.setProposalGovernmentSuggestDate(new Date());
			}
		}
		//主办
		if (role.getRoleId().equals(Constants.ROLE_ZHUBAN)) {
			if (proposal.getProposalStatus().equals(Constants.PROPOSAL_UNDERTAKE_DEPT_SUBMIT)
					|| proposal.getProposalStatus().equals(Constants.PROPOSAL_ZHUBAN_SUBMIT) 
					|| proposal.getProposalStatus().equals(Constants.PROPOSAL_HUIBAN_SUBMIT)) {
				proposal.setProposalStatus(Constants.PROPOSAL_ZHUBAN_SUBMIT);
				if (!Utils.isStringNull(proposal.getProposalRefuse())) {
					proposal.setProposalStatus(Constants.PROPOSAL_REFUSE);
				}
			}
		}
		//会办
		if (role.getRoleId().equals(Constants.ROLE_HUIBAN)) {
			if (proposal.getProposalStatus().equals(Constants.PROPOSAL_UNDERTAKE_DEPT_SUBMIT)
					|| proposal.getProposalStatus().equals(Constants.PROPOSAL_ZHUBAN_SUBMIT) 
					|| proposal.getProposalStatus().equals(Constants.PROPOSAL_HUIBAN_SUBMIT)) {
				proposal.setProposalStatus(Constants.PROPOSAL_HUIBAN_SUBMIT);
				if (!Utils.isStringNull(proposal.getProposalRefuse())) {
					proposal.setProposalStatus(Constants.PROPOSAL_REFUSE);
				}
			}
		}
		
		try {
			if (!Utils.isStringNull(proposal.getProposalContentBase64())) {
				proposal.setProposalContent(URLDecoder.decode(proposal.getProposalContentBase64(), "UTF-8"));
			}
			if (!Utils.isStringNull(proposal.getProposalAnswerBase64())) {
				proposal.setProposalAnswer(URLDecoder.decode(proposal.getProposalAnswerBase64(), "UTF-8"));
			}
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		try {
			service.modifyPOJOAndAnnex(proposal, request, annex, annexFileName, annexContentType, proposal.getProposalId());
		} catch (FileUploadException e) {
			setERROR(e.getMessage());
			e.printStackTrace();
			return MODIFY;
		} catch (SQLException e) {
			setERROR(e.getMessage());
			e.printStackTrace();
			return MODIFY;
		}
		return search();
	}
	public String detail() {
		modifyPre();
		return DETAIL;
	}

	public String group() {
		
		StringBuffer sql = new StringBuffer("select count(*) from Proposal proposal where 1=1");
		if (!Utils.isStringNull(s_proposalSession)) {
			sql.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
		}
		if (!Utils.isStringNull(s_proposalCount)) {
			sql.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
		}
		list = service.findPOJOList(sql.toString());
		String sum = list.get(0).toString();
		//立案数统计
		if (Utils.isStringNull(type)) {
			StringBuffer sql1 = new StringBuffer("select count(*) from Proposal proposal where proposalAduitSuggestSelect = '1'");
			if (!Utils.isStringNull(s_proposalSession)) {
				sql1.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
			}
			if (!Utils.isStringNull(s_proposalCount)) {
				sql1.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
			}
			List list1 = service.findPOJOList(sql1.toString());
			String value1 = list1.get(0).toString();
			String value2 = String.valueOf(Integer.valueOf(sum) - Integer.valueOf(value1));
			list.add(value1);
			list.add(Utils.divideScale(value1, sum) + "%");
			list.add(value2);
			list.add(Utils.divideScale(value2, sum) + "%");
		} else if ("1".equals(type)) {
			//集体提案统计
			StringBuffer sql1 = new StringBuffer("select count(*) from Proposal proposal where proposalIsGroupSelect = '1'");
			if (!Utils.isStringNull(s_proposalSession)) {
				sql1.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
			}
			if (!Utils.isStringNull(s_proposalCount)) {
				sql1.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
			}
			List list1 = service.findPOJOList(sql1.toString());
			String value1 = list1.get(0).toString();
			String value2 = String.valueOf(Integer.valueOf(sum) - Integer.valueOf(value1));
			list.add(value1);
			list.add(Utils.divideScale(value1, sum) + "%");
			list.add(value2);
			list.add(Utils.divideScale(value2, sum) + "%");
		} else if ("2".equals(type)) {
			//界别提案统计
			StringBuffer sql1 = new StringBuffer("select count(*), user.jieBie " +
					"from Proposal proposal, BaseUser user " +
					"where user.userId = proposal.proposalUserIdFirst group by user.jieBie");
			if (!Utils.isStringNull(s_proposalSession)) {
				sql1.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
			}
			if (!Utils.isStringNull(s_proposalCount)) {
				sql1.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
			}
			List list1 = service.findPOJOList(sql1.toString());
			for (int i = 1; i <= getPropertiesLength("jieBie"); i++) {
				List list2 = new ArrayList();
				list2.add(getPropertiesValue("jieBie", (i + "")));
				boolean isHaveValue = false;
				for (int j = 0; j < list1.size(); j++) {
					Object obj[] = (Object[]) list1.get(j);
					if (String.valueOf(i).equals(obj[1])) {
						String value = obj[0].toString();
						list2.add(value);
						list2.add(Utils.divideScale(value, sum) + "%");
						isHaveValue = true;
						break;
					} 
				}
				if (!isHaveValue) {
					list2.add(0);
					list2.add("0.00%");
				}
				list.add(list2);
			}
		} else if ("3".equals(type)) {
			//分类统计（ABC三类提案）
			StringBuffer sql1 = new StringBuffer("select count(*), proposalClassify from Proposal proposal group by proposalClassify");
			if (!Utils.isStringNull(s_proposalSession)) {
				sql1.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
			}
			if (!Utils.isStringNull(s_proposalCount)) {
				sql1.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
			}
			List list1 = service.findPOJOList(sql1.toString());
			for (int i = 0; i < getPropertiesLength("proposal.proposalClassify"); i++) {
				List list2 = new ArrayList();
				list2.add(getPropertiesValue("proposal.proposalClassify", (i + "")));
				boolean isHaveValue = false;
				for (int j = 0; j < list1.size(); j++) {
					Object obj[] = (Object[]) list1.get(j);
					if (String.valueOf(i).equals(obj[1])) {
						String value = obj[0].toString();
						list2.add(value);
						list2.add(Utils.divideScale(value, sum) + "%");
						isHaveValue = true;
						break;
					} 
				}
				if (!isHaveValue) {
					list2.add(0);
					list2.add("0.00%");
				}
				list.add(list2);
			}
		} else if ("4".equals(type)) {
			//办理结果满意度统计
			StringBuffer sql1 = new StringBuffer("select count(*), proposalSatisfy from Proposal proposal group by proposalSatisfy");
			if (!Utils.isStringNull(s_proposalSession)) {
				sql1.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
			}
			if (!Utils.isStringNull(s_proposalCount)) {
				sql1.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
			}
			List list1 = service.findPOJOList(sql1.toString());
			for (int i = 0; i < getPropertiesLength("proposal.proposalSatisfy"); i++) {
				List list2 = new ArrayList();
				String temp = getPropertiesValue("proposal.proposalSatisfy", (i + ""));
				if (Utils.isStringNull(temp)) {
					temp = "未选择";
				}
				list2.add(temp);
				boolean isHaveValue = false;
				for (int j = 0; j < list1.size(); j++) {
					Object obj[] = (Object[]) list1.get(j);
					if (String.valueOf(i).equals(obj[1])) {
						String value = obj[0].toString();
						list2.add(value);
						list2.add(Utils.divideScale(value, sum) + "%");
						isHaveValue = true;
						break;
					} 
				}
				if (!isHaveValue) {
					list2.add(0);
					list2.add("0.00%");
				}
				list.add(list2);
			}
		} else if ("5".equals(type)) {
			//审查意见统计
			StringBuffer sql1 = new StringBuffer("select count(*), proposalAduitSuggestSelect from Proposal proposal group by proposalAduitSuggestSelect");
			if (!Utils.isStringNull(s_proposalSession)) {
				sql1.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
			}
			if (!Utils.isStringNull(s_proposalCount)) {
				sql1.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
			}
			List list1 = service.findPOJOList(sql1.toString());
			for (int i = 0; i < getPropertiesLength("proposal.proposalAduitSuggestSelect"); i++) {
				List list2 = new ArrayList();
				String temp = getPropertiesValue("proposal.proposalAduitSuggestSelect", (i + ""));
				if (Utils.isStringNull(temp)) {
					temp = "未选择";
				}
				list2.add(temp);
				boolean isHaveValue = false;
				for (int j = 0; j < list1.size(); j++) {
					Object obj[] = (Object[]) list1.get(j);
					if (String.valueOf(i).equals(obj[1])) {
						String value = obj[0].toString();
						list2.add(value);
						list2.add(Utils.divideScale(value, sum) + "%");
						isHaveValue = true;
						break;
					} 
				}
				if (!isHaveValue) {
					list2.add(0);
					list2.add("0.00%");
				}
				list.add(list2);
			}
		} else if ("6".equals(type)) {
			//立案类型统计
			StringBuffer sql1 = new StringBuffer("select count(*), proposalRegisterTypeSelect from Proposal proposal group by proposalRegisterTypeSelect");
			if (!Utils.isStringNull(s_proposalSession)) {
				sql1.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
			}
			if (!Utils.isStringNull(s_proposalCount)) {
				sql1.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
			}
			List list1 = service.findPOJOList(sql1.toString());
			for (int i = 0; i < getPropertiesLength("proposal.proposalRegisterTypeSelect"); i++) {
				List list2 = new ArrayList();
				String temp = getPropertiesValue("proposal.proposalRegisterTypeSelect", (i + ""));
				if (Utils.isStringNull(temp)) {
					temp = "未选择";
				}
				list2.add(temp);
				boolean isHaveValue = false;
				for (int j = 0; j < list1.size(); j++) {
					Object obj[] = (Object[]) list1.get(j);
					if (String.valueOf(i).equals(obj[1])) {
						String value = obj[0].toString();
						list2.add(value);
						list2.add(Utils.divideScale(value, sum) + "%");
						isHaveValue = true;
						break;
					} 
				}
				if (!isHaveValue) {
					list2.add(0);
					list2.add("0.00%");
				}
				list.add(list2);
			}
		} else if ("7".equals(type)) {
			//案件状态统计
			StringBuffer sql1 = new StringBuffer("select count(*), proposalMethod from Proposal proposal group by proposalMethod");
			if (!Utils.isStringNull(s_proposalSession)) {
				sql1.append(" and (proposal.proposalSession like '%" + s_proposalSession+ "%')");
			}
			if (!Utils.isStringNull(s_proposalCount)) {
				sql1.append(" and (proposal.proposalCount like '%" + s_proposalCount+ "%')");
			}
			List list1 = service.findPOJOList(sql1.toString());
			for (int i = 1; i <= getPropertiesLength("proposal.proposalMethod"); i++) {
				List list2 = new ArrayList();
				String temp = getPropertiesValue("proposal.proposalMethod", (i + ""));
				if (Utils.isStringNull(temp)) {
					temp = "未选择";
				}
				list2.add(temp);
				boolean isHaveValue = false;
				for (int j = 0; j < list1.size(); j++) {
					Object obj[] = (Object[]) list1.get(j);
					if (String.valueOf(i).equals(obj[1])) {
						String value = obj[0].toString();
						list2.add(value);
						list2.add(Utils.divideScale(value, sum) + "%");
						isHaveValue = true;
						break;
					} 
				}
				if (!isHaveValue) {
					list2.add(0);
					list2.add("0.00%");
				}
				list.add(list2);
			}
		}
		
		return "group";
	}
	
	public String chooseProposalDept() {
		
		return "dept";
	}
	
	public String findProposalDept(){
		
		StringBuffer sql = new StringBuffer("from ProposalDept proposalDept where 1=1 ");
		if (!Utils.isStringNull(s_proposalDept)) {
			sql.append(" and (proposalDept.proposalDeptName like '%" + s_proposalDept+ "%')");
		}
		proposalDepts = service.findPOJOList(sql.toString());
		return "json";
	}
	//按部门统计数据导出excel
	public void outExcel() {
		try {
			search();
			OutputStream os = response.getOutputStream();// 取得输出流   
	        response.reset();// 清空输出流   
	        response.setHeader("Content-disposition", "attachment; filename=bumen.xls");// 设定输出文件头   
	        response.setContentType("application/msexcel");// 定义输出类型 
	        
	        WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件   
	        String tmptitle = "提案列表"; // 标题   
	        WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称  
	        // 设置excel标题   
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD, 
			                       false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   
			WritableCellFormat wcfFC = new WritableCellFormat(wfont); 
			wcfFC.setBackground(Colour.AQUA); 
			wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));   
			wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14,WritableFont.BOLD, 
			                   false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   
			wcfFC = new WritableCellFormat(wfont);  

			String titleArray[] = {
					
				  "类别编号",
				  "届次",
				  "案由",		 
				  "第一提案人",
				  "界别",
				  "联系电话（手机）",
				  "提案日期",	
				  "是否联名提案",
				  "审查意见",
				  "立案类型",
				  "提案分类",
				  "交办单位意见",
				  "承办单位意见",	
				  "满意度",	
				  "案件状态",	 
					
			};
			
			 
			for (int k = 0; k < titleArray.length; k++) {
				wsheet.addCell(new Label(k, 2, titleArray[k])); 
			}
			
			
			int rowSpace = 3;
			List list = new ArrayList();
			flipPageInfo.setSize(1000);
			do {
				search();
				list = flipPageInfo.getData();
				//总计行
				for (int i = 0; i < list.size(); i++) {
					Object []obj = (Object[]) list.get(i);
					Proposal proposal = (Proposal) obj[0];
					BaseUser user = (BaseUser) obj[1];
					
					wsheet.addCell(new Label(0, i+rowSpace, getPropertiesValue("proposal.proposalClassify", proposal.getProposalClassify(), true) + proposal.getProposalNo()+ "号"));  
					wsheet.addCell(new Label(1, i+rowSpace, proposal.getProposalSession() + "届" + proposal.getProposalCount() +"次")); 
					wsheet.addCell(new Label(2, i+rowSpace, proposal.getProposalBrief()));
					wsheet.addCell(new Label(3, i+rowSpace, user.getUserName())); 
					wsheet.addCell(new Label(4, i+rowSpace, getPropertiesValue("jieBie", user.getJieBie(), true)));
					wsheet.addCell(new Label(5, i+rowSpace, user.getUserPhone())); 
					wsheet.addCell(new Label(6, i+rowSpace, Utils.formatDateString(proposal.getProposalSubmitDate()))); 
					
					String proposalIsGroupSelect = getPropertiesValue("proposal.proposalIsGroupSelect", proposal.getProposalIsGroupSelect());
					if ("1".equals(proposal.getProposalIsGroupSelect())) {
						proposalIsGroupSelect += proposal.getProposalUnionUserName();
					}
					wsheet.addCell(new Label(7, i+rowSpace, proposalIsGroupSelect));  
					wsheet.addCell(new Label(8, i+rowSpace, getPropertiesValue("proposal.proposalAduitSuggestSelect", proposal.getProposalAduitSuggestSelect(), true)));  
					wsheet.addCell(new Label(9, i+rowSpace, getPropertiesValue("proposal.proposalRegisterTypeSelect", proposal.getProposalRegisterTypeSelect(), true)));  
					wsheet.addCell(new Label(10, i+rowSpace, getPropertiesValue("proposal.proposalClassify", proposal.getProposalClassify(), true)));  
					wsheet.addCell(new Label(11, i+rowSpace, getPropertiesValue("proposal.proposalUndertakeDeptSuggest", proposal.getProposalUndertakeDeptSuggest(), true)));  
					wsheet.addCell(new Label(12, i+rowSpace, getPropertiesValue("proposal.proposalTaketoDeptSuggest", proposal.getProposalTaketoDeptSuggest(), true)));  
					wsheet.addCell(new Label(13, i+rowSpace, getPropertiesValue("proposal.proposalSatisfy", proposal.getProposalSatisfy(), true)));  
					wsheet.addCell(new Label(14, i+rowSpace, getPropertiesValue("proposal.proposalMethod", proposal.getProposalMethod(), true)));  
					
				}
				flipPageInfo.setPage(flipPageInfo.getPage() + 1);
				rowSpace += flipPageInfo.getSize();
			} while (flipPageInfo.getPage() <= flipPageInfo.getPages());
			// 主体内容生成结束      
			// 写入文件   
		    wbook.write(); 
		    wbook.close();  
		    // 关闭流
		    os.close(); 
			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}		
	}
	public BaseUser getUser() {
		return user;
	}

	public void setUser(BaseUser user) {
		this.user = user;
	}

	@JSON(name="proposal")
	public Proposal getProposal() {
		return proposal;
	}

	public void setProposal(Proposal proposal) {
		this.proposal = proposal;
	}

	public FlipPageInfo getFlipPageInfo() {
		return flipPageInfo;
	}

	public void setFlipPageInfo(FlipPageInfo flipPageInfo) {
		this.flipPageInfo = flipPageInfo;
	}

	public String[] getAnnexContentType() {
		return annexContentType;
	}

	public void setAnnexContentType(String[] annexContentType) {
		this.annexContentType = annexContentType;
	}

	public String[] getAnnexFileName() {
		return annexFileName;
	}

	public void setAnnexFileName(String[] annexFileName) {
		this.annexFileName = annexFileName;
	}

	public BaseRole getRole() {
		return role;
	}

	public void setRole(BaseRole role) {
		this.role = role;
	}

	public Date getStartDate() {
		return startDate;
	}

	public void setStartDate(Date startDate) {
		this.startDate = startDate;
	}

	public Date getEndDate() {
		return endDate;
	}

	public void setEndDate(Date endDate) {
		this.endDate = endDate;
	}

	public String getS_proposalNo() {
		return s_proposalNo;
	}

	public void setS_proposalNo(String no) {
		s_proposalNo = no;
	}

	public String getS_proposalSession() {
		return s_proposalSession;
	}

	public void setS_proposalSession(String session) {
		s_proposalSession = session;
	}

	public String getS_proposalBrief() {
		return s_proposalBrief;
	}

	public void setS_proposalBrief(String brief) {
		s_proposalBrief = brief;
	}

	public String getS_proposalUserNameFirst() {
		return s_proposalUserNameFirst;
	}

	public void setS_proposalUserNameFirst(String userNameFirst) {
		s_proposalUserNameFirst = userNameFirst;
	}

	public String getS_proposalPrincipal() {
		return s_proposalPrincipal;
	}

	public void setS_proposalPrincipal(String principal) {
		s_proposalPrincipal = principal;
	}

	public List getAnnexList() {
		return annexList;
	}

	public void setAnnexList(List annexList) {
		this.annexList = annexList;
	}

	public File[] getAnnex() {
		return annex;
	}

	public void setAnnex(File[] annex) {
		this.annex = annex;
	}

	public BaseDept getDept() {
		return dept;
	}

	public void setDept(BaseDept dept) {
		this.dept = dept;
	}

	public List getList() {
		return list;
	}

	public void setList(List list) {
		this.list = list;
	}

	public String getS_workUnit() {
		return s_workUnit;
	}

	public void setS_workUnit(String unit) {
		s_workUnit = unit;
	}

	public String getS_jieBie() {
		return s_jieBie;
	}

	public void setS_jieBie(String bie) {
		s_jieBie = bie;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getS_proposalCount() {
		return s_proposalCount;
	}

	public void setS_proposalCount(String count) {
		s_proposalCount = count;
	}

	@Override
	public void prepare() throws Exception {
		role = (BaseRole) session.get(Constants.SESSION_ROLE);
		user = (BaseUser) session.get(Constants.SESSION_USER);
		dept = (BaseDept) session.get(Constants.SESSION_DEPT);
	}

	public Tianjieci getTianjieci() {
		return tianjieci;
	}

	public void setTianjieci(Tianjieci tianjieci) {
		this.tianjieci = tianjieci;
	}

	public String getS_proposalClass() {
		return s_proposalClass;
	}

	public void setS_proposalClass(String class1) {
		s_proposalClass = class1;
	}

	public String getS_proposalAduitSuggestSelect() {
		return s_proposalAduitSuggestSelect;
	}

	public void setS_proposalAduitSuggestSelect(String aduitSuggestSelect) {
		s_proposalAduitSuggestSelect = aduitSuggestSelect;
	}

	public String getS_proposalRegisterTypeSelect() {
		return s_proposalRegisterTypeSelect;
	}

	public void setS_proposalRegisterTypeSelect(String registerTypeSelect) {
		s_proposalRegisterTypeSelect = registerTypeSelect;
	}

	public String getS_proposalClassify() {
		return s_proposalClassify;
	}

	public void setS_proposalClassify(String classify) {
		s_proposalClassify = classify;
	}

	public String getS_my() {
		return s_my;
	}

	public void setS_my(String s_my) {
		this.s_my = s_my;
	}

	public String getS_proposalSponsor() {
		return s_proposalSponsor;
	}

	public void setS_proposalSponsor(String sponsor) {
		s_proposalSponsor = sponsor;
	}

	public String getS_proposalNeeddo() {
		return s_proposalNeeddo;
	}

	public void setS_proposalNeeddo(String needdo) {
		s_proposalNeeddo = needdo;
	}

	public String getProposalTaketoDeptName() {
		return proposalTaketoDeptName;
	}

	public void setProposalTaketoDeptName(String proposalTaketoDeptName) {
		this.proposalTaketoDeptName = proposalTaketoDeptName;
	}

	public String getS_proposalMethod() {
		return s_proposalMethod;
	}

	public void setS_proposalMethod(String method) {
		s_proposalMethod = method;
	}

	public String getS_proposalSatisfy() {
		return s_proposalSatisfy;
	}

	public void setS_proposalSatisfy(String satisfy) {
		s_proposalSatisfy = satisfy;
	}
	public List<ProposalDept> getProposalDepts() {
		return proposalDepts;
	}

	public void setProposalDepts(List<ProposalDept> proposalDepts) {
		this.proposalDepts = proposalDepts;
	}

	public String getS_proposalDept() {
		return s_proposalDept;
	}

	public void setS_proposalDept(String dept) {
		s_proposalDept = dept;
	}

	public String getS_proposalDealStatus() {
		return s_proposalDealStatus;
	}

	public void setS_proposalDealStatus(String dealStatus) {
		s_proposalDealStatus = dealStatus;
	}

	public String getS_proposalSponsorId() {
		return s_proposalSponsorId;
	}

	public void setS_proposalSponsorId(String sponsorId) {
		s_proposalSponsorId = sponsorId;
	}

	public String getS_proposalNeeddoId() {
		return s_proposalNeeddoId;
	}

	public void setS_proposalNeeddoId(String needdoId) {
		s_proposalNeeddoId = needdoId;
	}

	public String getS_proposalStaffStatus() {
		return s_proposalStaffStatus;
	}

	public void setS_proposalStaffStatus(String staffStatus) {
		s_proposalStaffStatus = staffStatus;
	}

	public String getS_proposalUndertakeDeptSuggest() {
		return s_proposalUndertakeDeptSuggest;
	}

	public void setS_proposalUndertakeDeptSuggest(String undertakeDeptSuggest) {
		s_proposalUndertakeDeptSuggest = undertakeDeptSuggest;
	}

	public String getS_proposalTaketoDept() {
		return s_proposalTaketoDept;
	}

	public void setS_proposalTaketoDept(String taketoDept) {
		s_proposalTaketoDept = taketoDept;
	}
	
	
}
